教你从零开始，设计一个收支表格
作者 l Skye
来自优秀学员投稿
Skye是我Excel进阶训练营里的优秀学员。
第1期训练营的时候，Skye和其他的Excel小白一样，提个问题能把我气死：
1- 有高手在吗？怎么快速提取出一天最早和最晚的时间呢？
2- 我要是自己去百度，还要老师干嘛？
3- 谁能给我个Excel安装包？
经过了2期Excel进阶训练营，无微不至的「蹂躏」，你可能想象不到，这个差点把我气死的学员，开始在公司内部组织培训了。
从简单入门的快捷键，高效操作的小技巧，到表格设计思维和方法，Skye成为了一个名副其实的Excel高手。
不啰嗦了，说多了，你可能觉得我为学员自吹自擂，以下是Skye姐的投稿，大家一起来感受一下吧。
同事静静跑来说：
skye姐，我妈说现在都有年度账单，要我也每天记账，年底要交年度账单给她。
我妈给了我一张表让我记录自己的日常开支，可是我觉得用起来特别不方便，你给看看。
接着静静开始抱怨起来：
你看我，要查询一笔记录久了查询起来很费时间
每天的收支用计算器算好还要验算几遍就怕出错不好找
要是哪天收入支出项目一多，格子都不够写
skye姐你快帮我想想办法吧。
静静的抱怨，你看着肯定很眼熟吧？在设计表格的时候，只想着如何看着更直观，完全没有考虑到后面的查询和统计。
就像上面这个门锁，纵使我们设置了两个不同类型的锁，如果一开始「没有搞清楚」门的打开方式，再多门锁也是徒劳。
表格的设计一定要从全局考虑，保证下面3点，才能做出好用的表格：
1- 记录表，方便记录的数据表
2- 查询表，可以方便的查询不同状态的数据
3- 统计表，可以快速的根据各种不同口径，统计数据
2-分钟后，Skye姐把设计好的收支表格发给了静静。
正如你所看到的，设计后的表格，只需要依次添加数据，然后再右边刷新一下，就可以快速更新统计结果。
- 数据记录起来更方便，不用根据日期去找填写位置。
- 数据统计起来更高效，只要刷新一下就可以了
表格的设计过程，主要包含两个部分：
收支明细表（数据源）：记录每一笔收支，遗漏的项目继续添加即可。通过筛选可以方便的进行单笔查询，通过数据有效性，规范数据输入，避免误操作造成数据统计误差
汇总表（数据汇总）：汇总各类收支，通过条件格式，收支为负值会示警
具体怎么实现的呢？
Skye姐把制作过程，逐字逐句的讲给了静静。
01 | 分析需求
Excel设计表格，数据源使用一维表会更方便函数和数据透视表的使用
根据静静的需求，需要以下2类数据
1- 收支明细（数据源）：日期、项目、收入支出金额
2- 汇总（数据透视表）：总收入、总支出、收支合计
02 | 制作步骤
第1步，设计表头
收支明细和汇总好比是食材和菜品的关系，收支明细的食材是
「日期」「收支项目」「收支金额」，再添加一个「备注栏」可以对收支做一些说明，查询的时候也方便回忆。
设计好的表头是这样的：
第2步，输入数据
通过1，2这2个步骤，明细表就完成了
这样在添加数据的时候，只需要在最后一样下面，逐一的添加数据就可以了，不用再根据日期，查找填写的位置了。
第3步，汇总（数据透视表）
之所以要把数据记录的表格，设计成从上往下添加的数据结构，实际上是在为统计数据做准备。
因为Excel中有一个高效的数据统计神器：数据透视表。
使用透视表，整个统计过程，不费吹灰之力就可以轻松完成，大致的步骤是这样的：
1- 选中数据源-【插入】-【数据透视表】。
2- 设置行字段【项目】列字段「收支合计」「收入」「支出」，总计行输出所需总收入、总支出、收支合计。
只是鼠标拖一拖、拽一拽就完成了数据统计。
整个设计和统计过程，10分钟都不到，静静看的目瞪口呆
静静：Skye姐，你太厉害了，还有还有。。。
- 这怎么汇总里有餐费 午餐 晚餐……
- 香港预订酒店也可以归类到旅行吧……
- 自学这笔支出跑到收入里了……
- 还有还有，能不能看出每笔支出后的余额？
- 每次新增收支，汇总表刷新不出来？
Skye姐：咦，这么多问题，这是我考虑不周，你稍等，再给我10分钟优化一下。
03 | 优化方案
根据静静的要求，需要对表格进行3个方面优化
1- 项目类别太杂乱，可以通过数据有效性规范
2- 显示余额增加一个小公式就可以啦
3- 透视表实时更新可以将数据源收支明细表升级为超级表格
第1步：增加字段
首先在表头增加一个余额的字段，实时的计算余额。
第2步：设置超级表格
接下来，为了让透视表的统计结果，能够随着原始数据自动更新，需要把收支明细表格设置为超级表格
这样，数据源表（收支明细表）设置为超级表格后，新增数据后，对透视表进行刷新就能及时反馈汇总。
1- 选择表内任意单元格【Ctrl+T】-勾选表包含标题
或者，数据源-【插入】-【表格】-勾选包含表标题-【确定】就得到下面这张表，新增项目公式自动填充
第3步：规范输入
表格设计的一个重要环节就是「规范输入」，因为你永远都无法控制队友，把表格填写的五花八门。
Excel规范数据输入行为，有一个非常好用的功能叫做：数据验证。
以日期列为例，通过【数据】-【数据验证】来完成规范输入的操作如下：
1- 选中列-【数据】-【有效性】-【日期】-【大于等于】设置大于当前日期-【确定】
在Excel表格中，认可2种日期输入方式：2019-1-1或者2019/1/1。
其他的输入方式比如2019.1.1、20190101等等Excel都不认为是日期格式。
在进行透视表汇总时会造成不能识别的日期而统计数据不全，因此需要对日期进行输入规范设置
参考日期列的规范方式，把收入、和支出列，也设置好对应的「数据验证」规则。
收入：选中列-【数据】-【有效性】-【小数】-【大于等于】-0-【确定】
支出：选中列-【数据】-【有效性】-【小数】-【小于等于】-0-【确定】
对收入支出进行规范数据有效性规范也是为了减少错误发生，避免汇总统计错误
项目输入规范：需要通过2个步骤完成，
1- 设置项目条件格式数据来源
2- 设置数据有效性
①设置辅助列项目数据源：选中数据源-【插入】-【表格】-勾选包含表标题-【确定】或者【Ctrl+T】
②设置有效性：选中列-【数据】-【有效性】-【序列】-【来源】=INDIRECT（$N$4）-【确定】
经过以上2步后，在辅助列添加新增项目后，数据源表中项目下拉菜单会自动更新
第4步：汇总（数据透视表）
选中数据源【插入】-【数据透视表】-设置行字段【项目】列字段【收支合计、收入、支出】，总计行输出所需总收入、总支出、收支合计。
在收支合计设置条件格式【开始】-【条件格式】-【突出显示单元格规则】-【小于】0
当收支合计为负值会突出显示报警
每天填写完新增收支后，点击数据透视表-【右键】-【刷新】完成数据汇总
第5步：说明
在设计表格的时候，一定不要把每个人都想象成自己，拿过来就会使用这个表格，这是不可能的。
把每个用户都想象成超级Excel小白，他们不知道什么条件格式，什么数据有效性。
在表格里写上说明，是一个可以有效避免疑问和错误的方法！
04 | 优化后效果
跟着Skye姐学习完整个设计和制作过程之后，回过头来再来看这个表格，你会更直观的看到，这个表格的设计优点：
1- 每一笔新增支出后，刷新透视表即可反馈汇总
2- 对汇总进行条件格式设定，如果收支合计为负值，显示红色预警
3- 遗漏的收支记录随时可以继续添加
4- 日期、收支栏设置数据验证后，避免误输入造成统计错误
5- 项目栏条件格式用辅助列进行数据验证，既规范了输入又随时可以新增
6- 需要查询某项支出通过日期、关键词都能方便的查找
7- 贴心的为表格写上说明，输入时遇到弹窗警告也不怕啦~
05 | 知识点总结
虽然收支表格是一个很简单的需求，但是越是简单的事情，越能清晰的体现你的逻辑能力。
武林高手过招，只需要一举手一投足，就能感受到对方的功力。
本节课主要涉及到的知识点有：
1- 规范日期、金额输入：通过【数据验证】-【有效性】进行设置
2- 数据验证序列字段新增：【数据验证】+函数【INDIRECT】引用超级表格
3- 超级表格。自动扩展数据区域，让透视表自动更新。
06 | 表格还可以这样做
一堆冷冰冰的数字，正数和负数纵横交错，考验着我们的视力和耐力。
我想没有人会拒绝Skye姐，把上面的统计结果，变成下面这样的数据报告吧？
这个图表是怎么做出来的呢？
咱们明天继续请Skye姐，做客拉小登，给我们聊一聊图表的设计心得。
Skye，秋叶Excel进阶训练营优秀学员
好的表格是设计出来的，绝对不是用长公式堆砌出来的。
想学习表格设计思维？
想学习高效统计方法？
扫码下面二维码，添加秋小E免费预约咨询。
我是拉小登，源于Excel，不只是Excel